1% completed
Problem
Table Activities:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.
Problem Definition
Write a solution to find for each date the number of different products sold and their names.
The sold products names for each date should be sorted lexicographically.
Return the result table ordered by sell_date.
Example
Output
Try It Yourself
Activities =
Solution
To solve this problem, the approach involves using SQL queries to analyze the Activities table and find the number of different products sold and their names for each date. The desired output requires counting the distinct products and listing their names for each unique sell_date.
The COUNT function with the DISTINCT keyword is used to calculate the number of different products sold on each date. This information is captured in the "num_sold" column. Additionally, the GROUP_CONCAT function concatenates the distinct product names for each date. The ORDER BY clause is used within the GROUP_CONCAT function to ensure that the product names are sorted lexicographically.
The results are then grouped by sell_date using the GROUP BY clause, ensuring that the calculations are performed for each unique date. The final step involves ordering the result table by sell_date in ascending order, as the problem statement specifies.
SELECT sell_date, Count(DISTINCT product) AS num_sold, Group_concat(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products FROM Activities GROUP BY sell_date ORDER BY sell_date ASC;
Let's break down the query step by step:
Step 1: Count the Number of Different Products Sold for Each Date
We use the COUNT(DISTINCT product) to count the number of different products sold for each date.
SELECT sell_date, COUNT(DISTINCT product) AS num_sold FROM activities GROUP BY sell_date;
Output After Step 1:
+------------+----------+ | sell_date | num_sold | +------------+----------+ | 2020-05-30 | 3 | | 2020-06-01 | 2 | | 2020-06-02 | 1 | +------------+----------+
Step 2: Concatenate Sorted Product Names for Each Date
We use GROUP_CONCAT to concatenate the product names for each date, sorting them lexicographically.
SELECT sell_date, num_sold, GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products FROM ( -- Sub-Step 1 output goes here ) AS subquery GROUP BY sell_date;
Output After Step 2:
+------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+
Step 3: Order the Result
We order the result table by sell_date.
SELECT sell_date, num_sold, products FROM ( -- Sub-Step 2 output goes here ) AS final_result ORDER BY sell_date ASC;
Final Output:
+------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+
On this page
Problem
Problem Definition
Example
Output
Try It Yourself
Solution
Step 1: Count the Number of Different Products Sold for Each Date
Step 2: Concatenate Sorted Product Names for Each Date
Step 3: Order the Result